﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Entities;

namespace DataAccessLayer
{
    public class HocVienDAL : DBConnection
    {
        public List<HocVienEntity> getAll()
        {
            List<HocVienEntity> lst = new List<HocVienEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("hoc_vien_getAll", con);
                cmd.CommandType = CommandType.StoredProcedure;

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    HocVienEntity obj = new HocVienEntity();
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.HoTen = Convert.ToString(dr["HoTen"]);
                    obj.SDT = Convert.ToDecimal(dr["SDT"]);
                    obj.Email = Convert.ToString(dr["Email"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public HocVienEntity getById(string _id)
        {
            HocVienEntity obj = new HocVienEntity();
            try
            {
                MySqlCommand cmd = new MySqlCommand("hoc_vien_getById", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _id.ToLower().Trim());

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.Read())
                {
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.HoTen = Convert.ToString(dr["HoTen"]);
                    obj.SDT = Convert.ToDecimal(dr["SDT"]);
                    obj.Email = Convert.ToString(dr["Email"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return obj;
        }

        public List<HocVienEntity> getByKhoaHocId(int _id)
        {
            List<HocVienEntity> lst = new List<HocVienEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("hoc_vien_getByKhoaHocId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maKhoaHoc", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    HocVienEntity obj = new HocVienEntity();
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.HoTen = Convert.ToString(dr["HoTen"]);
                    obj.SDT = Convert.ToDecimal(dr["SDT"]);
                    obj.Email = Convert.ToString(dr["Email"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public List<HocVienEntity> getByLopHocId(int _id)
        {
            List<HocVienEntity> lst = new List<HocVienEntity>();
            try
            {
                MySqlCommand cmd = new MySqlCommand("hoc_vien_getByLopHocId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maLopHoc", _id);

                if (con.State != ConnectionState.Open)
                    con.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                while (dr.Read())
                {
                    HocVienEntity obj = new HocVienEntity();
                    obj.MaHocVien = Convert.ToString(dr["MaHocVien"]);
                    obj.HoTen = Convert.ToString(dr["HoTen"]);
                    obj.SDT = Convert.ToDecimal(dr["SDT"]);
                    obj.Email = Convert.ToString(dr["Email"]);
                    obj.GhiChu = Convert.ToString(dr["GhiChu"]);
                    lst.Add(obj);
                }
                dr.Close();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return lst;
        }

        public int addHocVien(HocVienEntity _obj)
        {
            int result = 0;
            try
            {
                HocVienEntity hv = getById(_obj.MaHocVien);
                if (hv.MaHocVien != null)
                    throw new Exception("Ma Hoc Vien da ton tai");

                MySqlCommand cmd = new MySqlCommand("hoc_vien_insert", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _obj.MaHocVien);
                cmd.Parameters.AddWithValue("hoTen", _obj.HoTen);
                cmd.Parameters.AddWithValue("sDT", _obj.SDT);
                cmd.Parameters.AddWithValue("email", _obj.Email);
                cmd.Parameters.AddWithValue("ghiChu", _obj.GhiChu);

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int deleteHocVien(string _id)
        {
            int result = 0;
            try
            {
                MySqlCommand cmd = new MySqlCommand("hoc_vien_delete", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _id);
                cmd.Parameters["maHocVien"].Direction = ParameterDirection.Input;

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

        public int updateHocVien(HocVienEntity _obj)
        {
            int result = 0;
            try
            {

                MySqlCommand cmd = new MySqlCommand("hoc_vien_update", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("maHocVien", _obj.MaHocVien);
                cmd.Parameters.AddWithValue("hoTen", _obj.HoTen);
                cmd.Parameters.AddWithValue("sDT", _obj.SDT);
                cmd.Parameters.AddWithValue("email", _obj.Email);
                cmd.Parameters.AddWithValue("ghiChu", _obj.GhiChu);

                if (con.State != ConnectionState.Open)
                    con.Open();
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return result;
        }

    }
}
